﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using SqlserverHelper;
using System.Threading;

using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using OJ;
using DLLmySQL;
using System.Text;
using System.IO;

/// <summary>
/// 9、查询店铺分析
/// </summary>
public partial class getDataDPFX : System.Web.UI.Page
{
    public string strCnn = ConfigurationSettings.AppSettings["DataBaseString"];
    protected void Page_Load(object sender, EventArgs e)
    {
        string shop_code = "";
        string day_begin = "";
        string day_end = "";
        string is_export = "false";
        string user_identity = "";

        if (Request.Form["shop_code"] != null && Request.Form["shop_code"].ToString().Trim() != "")
        {
            shop_code = Request.Form["shop_code"].ToString();
        }
        if (Request.Form["day_begin"] != null && Request.Form["day_begin"].ToString().Trim() != "")
        {
            day_begin = Request.Form["day_begin"].ToString();
        }
        if (Request.Form["day_end"] != null && Request.Form["day_end"].ToString().Trim() != "")
        {
            day_end = Request.Form["day_end"].ToString();
        }
        if (Request.Form["is_export"] != null && Request.Form["is_export"].ToString().Trim() != "")
        {
            is_export = Request.Form["is_export"].ToString();
        }
        if (Request.Form["user_identity"] != null && Request.Form["user_identity"].ToString().Trim() != "")
        {
            user_identity = Request.Form["user_identity"].ToString();
        }


        string stResult = f_getDataDPFX(shop_code, day_begin, day_end, is_export, user_identity);
        Response.Write(stResult);
    }

    public string f_getDataDPFX(string shop_code, string day_begin, string day_end, string is_export,string user_identity)
    {

        Define.retDPFX ret = new Define.retDPFX();
        List<Define.DPFX> L = new List<Define.DPFX>();
        List<Define.lineChartData> L1 = new List<Define.lineChartData>();

        try
        { 
            //1
            string strQuery = "SELECT DATE_FORMAT(ymd,'%Y-%m-%d'),dept_name," +
                "SUM(sell_amt) AS total_sell_amt,  ROUND(SUM(sell_amt) / COUNT(DISTINCT order_id), 2) AS kdj  " +
                "FROM  dsn_sell ,dsh_prd  ";
            strQuery += " where 1=1 ";
            strQuery += " and dsn_sell.prd_code = dsh_prd.class_id ";
            strQuery += " and dsh_prd.user_identity ='"+ user_identity + "' ";
            if (shop_code != "")
            {
                strQuery = strQuery + "and dept_name like '%" + shop_code.Trim() + "%'";
            }
            if (day_begin == "" && day_end == "")
            {
                strQuery = strQuery + " and ymd BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) AND NOW()";
            }
            if (day_begin != "")
            {
                strQuery = strQuery + " and ymd >= '" + day_begin.Trim() + "' 00:00:00";
            }
            if (day_end != "")
            {
                strQuery = strQuery + " and ymd <= '" + day_end.Trim() + "' 23:59:59";
            }

            strQuery = strQuery + " GROUP BY DATE_FORMAT(ymd,'%Y-%m-%d'),dept_name";
            string outputFile = "";
            string outputUrl = "https://" + Request.Url.Authority;
            if (is_export == "true")
            {
                DataTable DT = CmySQL.GetDataTbale(strCnn, strQuery);
                if (DT != null && DT.Rows.Count > 0)
                {
                    /*for (int i = 0; i < DT.Rows.Count; i++)
                    {
                        Define.DPFX r = new Define.DPFX();
                        r.ymd = DT.Rows[i][0].ToString().Trim();
                        r.dept_name = DT.Rows[i][1].ToString().Trim();
                        r.qty = DT.Rows[i][2].ToString().Trim(); //总金额
                        r.kdj = DT.Rows[i][3].ToString().Trim(); //客单价
                        L.Add(r);
                    }*/
                }

                string fileName = "out_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".csv";
                outputFile = AppDomain.CurrentDomain.BaseDirectory + "out\\" + fileName;
                outputUrl = outputUrl + "/api_sst/out" + "/" + fileName;
                ExportToCsv(DT, outputFile);

                OperLog.log("店铺分析-导出", user_identity);
            }
            else {
                outputUrl = "";
                OperLog.log("店铺分析-查询", user_identity);

                //2
                string strQuery2 = "SELECT DATE_FORMAT(ymd,'%Y-%m-%d'),SUM(sell_price)/COUNT(*) kdj ,SUM(sell_price) zs,COUNT(*) jkl";
                strQuery2 += " FROM dsn_sell ,dsh_prd  ";
                strQuery2 += " WHERE 1=1 ";
                strQuery2 += " AND dsn_sell.prd_code = dsh_prd.class_id ";
                strQuery2 += " AND dsh_prd.user_identity ='" + user_identity + "' ";
                // strQuery2 += " AND DATE_FORMAT(ymd,'%Y-%m-%d') >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) ";

                if (shop_code != "")
                {
                    strQuery2 += " and dept_name like '%" + shop_code.Trim() + "%'";
                }
                if (day_begin == "" && day_end == "")
                {
                    strQuery2 += " and DATE_FORMAT(ymd,'%Y-%m-%d') BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) AND NOW()";
                }
                if (day_begin != "")
                {
                    strQuery2 += " and DATE_FORMAT(ymd,'%Y-%m-%d') >= '" + day_begin.Trim() + "'";
                }
                if (day_end != "")
                {
                    strQuery2 += " and DATE_FORMAT(ymd,'%Y-%m-%d') <= '" + day_end.Trim() + "'";
                }
                strQuery2 +=  " GROUP BY DATE_FORMAT(ymd,'%Y-%m-%d')";


                LogBLL.log("【店铺分析】【查询】" + strQuery2);
                DataTable DT2 = CmySQL.GetDataTbale(strCnn, strQuery2);

                if (DT2 != null && DT2.Rows.Count > 0)
                {
                    for (int i = 0; i < DT2.Rows.Count; i++)
                    {
                        Define.lineChartData r1 = new Define.lineChartData();
                        r1.name = DT2.Rows[i][0].ToString().Trim();
                        r1.data = DT2.Rows[i][1].ToString().Trim();
                        r1.data2 = DT2.Rows[i]["jkl"].ToString().Trim();

                        L1.Add(r1);
                    }
                }
            }

            

            ret.code = 200;
            ret.msg = "success";

            ret.data = L;
            ret.lineChartData = L1;
            ret.outputUrl = outputUrl;
            

        }
        catch (Exception e)
        {
            string er = e.Message;
            ret.code = 20000;
            ret.msg = er;
            ret.data = null;
            ret.lineChartData = null;
        }
        return Ojson.ObjectToJson(ret);
    }

    /// <summary>
    /// DataTable导出成CSV文件
    /// </summary>
    /// <param name="dataTable">数据集</param>
    /// <param name="filePath">导出文件地址</param>
    public void ExportToCsv(DataTable dataTable, string filePath)
    {
        StringBuilder sb = new StringBuilder();
        //foreach (DataColumn col in dataTable.Columns)
        //{
        //    sb.Append(col.ColumnName + ",");
        //}
        //sb.Append("进货");
        sb.Append("日期,店铺,总金额,客单价");

        sb.AppendLine();
        foreach (DataRow row in dataTable.Rows) 
        {
            foreach (DataColumn col in dataTable.Columns)
            {
                sb.Append(row[col].ToString().Replace(",", " ") + ",");
            }
            sb.AppendLine();
        }

        using (StreamWriter writer = new StreamWriter(filePath))
        {
            writer.Write(sb.ToString());
        }
    }
}